/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import main.*;
import bean.Documento;
import bean.Factura;
import java.sql.*;
import java.util.ArrayList;
import javax.swing.table.DefaultTableModel;
import views.PanelFactura;

/**
 *
 * @author LiziTa
 */
class SQLServerDAOFacturacion implements DAOFacturacion {
    
    @Override
     public float TotalFac(int idDoc) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        float resp=0;
         try { 
         Driver myDriver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
            String connectionUrl =  "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                                    "databaseName=inf282;user=inf282;password=inf282db;";
            conn = DriverManager.getConnection(connectionUrl);
                    
            pstmt = conn.prepareStatement("SELECT SUM(G04_SERVICIO.PRECIO_BASE) TOTAL FROM G04_SERVICIOXCONTENEDOR,G04_SERVICIO,G04_DOCUMENTOXCONTENEDOR"+
    " WHERE G04_DOCUMENTOXCONTENEDOR.IDDOCUMENTO="+idDoc+" AND G04_DOCUMENTOXCONTENEDOR.IDCONTENEDOR=G04_SERVICIOXCONTENEDOR.IDCONTENEDOR"+  
    " AND G04_SERVICIOXCONTENEDOR.IDSERVICIO=G04_SERVICIO.IDSERVICIO");
             rs =  pstmt.executeQuery();
             while (rs.next()){
                 resp=rs.getFloat("TOTAL");
             }
            
     }            
         catch (Exception ex) {
            ex.printStackTrace();
        } finally {
             try {if (rs != null) rs.close(); } 
             catch(Exception e){e.printStackTrace();}  
        } 
    
            return resp;
    }

    @Override
    public Factura queryByIdF(String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int agregaFactura(Factura f) {
        
        int result=0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {            
            Driver myDriver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
            String connectionUrl =  "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                                    "databaseName=inf282;user=inf282;password=inf282db;";
            
                String idFactura=String.valueOf(f.getId());
                String idUser=String.valueOf(f.getId());
                String iddoc=String.valueOf(f.getIdDoc());
                String igv=String.valueOf(f.getIgv());
                String total=String.valueOf(f.getTotal());
            conn = DriverManager.getConnection(connectionUrl);
            pstmt = conn.prepareStatement("INSERT INTO G04_FACTURA"
                    + "(IDFACTURA,IDUSUARIO,IDDOCUMENTO,ESTADO_PAGO,FECHA_EMISION,FECHA_VENCIMIENTO,IGV,MORA,TIPO_PAGO,MONEDA,TOTAL) "
                    + "VALUES("+idFactura+","
                    +idUser+","
                    +iddoc+",'"
                    + "Pendiente',GETDATE(),DATEADD(DAY,+24,GETDATE()),"
                    +igv+",0,'Factura','Soles',"
                    +total+")");
            
            rs =  pstmt.executeQuery();
           
                
               
            
                        
            if (result == 0){
                throw new Exception();
            }            
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
             //5. Se cierra la conexión
             try {if (rs != null) rs.close(); } 
             catch(Exception e){e.printStackTrace();}  
        } 
        return result;
    }

    @Override
    public int update(Factura f) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int delete(String idFactura) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ArrayList<Factura> queryByNF(String nombre) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public int queryallFac(String idDoc) {
          int resul=0;
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
    try {
            //1. Se registra el driver de la BD
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
           // Driver myDriver = new com.microsoft.sqlserver.jdbc.SQLServerDriver();
            //2. Se abre la conexión
            String connectionUrl = "jdbc:sqlserver://inti.lab.inf.pucp.edu.pe:1433;"+
                    "databaseName=inf282;user=inf282;password=inf282db;";
            conn = DriverManager.getConnection(connectionUrl);
            //3. Se ejecuta la sentencia SQL
            
           
            pstmt = conn.prepareStatement("select G04_SERVICIOXCONTENEDOR.IDCONTENEDOR, G04_SERVICIO.DESCRIPCION, G04_SERVICIOXCONTENEDOR.IDCONTENEDOR, G04_SERVICIO.PRECIO_BASE"
            +" from G04_SERVICIOXCONTENEDOR,G04_SERVICIO,G04_DOCUMENTOXCONTENEDOR"
            +" where G04_DOCUMENTOXCONTENEDOR.IDDOCUMENTO='"+idDoc+"'"
            +" and G04_DOCUMENTOXCONTENEDOR.IDCONTENEDOR=G04_SERVICIOXCONTENEDOR.IDCONTENEDOR"
            +" and G04_SERVICIOXCONTENEDOR.IDSERVICIO=G04_SERVICIO.IDSERVICIO");
            rs =  pstmt.executeQuery();
           
            //4. Se evalúan los resultados
            while (rs.next()){
                int id = rs.getInt("IDSERVICIO");
                String descripcion = rs.getString("DESCRIPCION");
                float preciobase = rs.getFloat("PRECIO_BASE"); 
                String unidadcobro = rs.getString("UNIDAD_COBRO");
                resul=1;               
               
              
            }               
        } catch (ClassNotFoundException | SQLException ex) {
        } finally {
             //5. Se cierra la conexión
             try {if (rs != null) rs.close(); } 
             catch(Exception e){}  
        } 
    
   return resul;
    }

}
